package com.ankicoo.office.controller;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.support.StandardMultipartHttpServletRequest;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;


/**
 * Description: 文档解析服务
 *
 * @author : QCYANG
 * @date : 2022/9/19 11:18
 */
@Controller
@RestController
@RequestMapping("/poi")
public class DocxParseController {

    @GetMapping("/upload")
    public void wordParse(StandardMultipartHttpServletRequest request, HttpServletResponse response)throws Exception {
        if (request.getFileMap().isEmpty()) {
            PrintWriter out;
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/json; charset=utf-8");
            out = response.getWriter();
            ObjectMapper mapper = new ObjectMapper();
            out.write(mapper.writeValueAsString("请上传正确格式的文件"));
        } else {
            for (MultipartFile multiFile : request.getFileMap().values()) {
                InputStream ins;
                if (!multiFile.isEmpty() && multiFile.getOriginalFilename() != null) {
                    ins = multiFile.getInputStream();
                } else {
                    throw new RuntimeException("文件内容为空");
                }
                ins.close();
//                generate();
//                reader("example.xlsb");
                reader("template2.xlsb");
            }
        }
    }

    private void generate(){
        Workbook workbook = new XSSFWorkbook(); // 创建xlsb文件实例
        Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个工作表

        // 创建一行并添加数据
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hello, World!");

        // 写入文件
        try (FileOutputStream outputStream = new FileOutputStream("example.xlsb")) {
            workbook.write(outputStream); // 写入文件系统
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close(); // 关闭工作簿资源
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void reader(String file){
        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(file))) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            for (Row row : sheet) { // 遍历行
                for (Cell cell : row) { // 遍历列
                    // 获取单元格数据，根据单元格类型使用相应的方法
                    switch (cell.getCellType()) {
                        case 1:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case 0:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case 4:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        // ...其他类型处理
                        default:
                            System.out.print("\t");
                            break;
                    }
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    /**
     * 将文本中某一个段落 替换为另一个文本全部内容
     * 传入 段落 paragraph
     * 传入 文本 document
     */
    @GetMapping("/uploadParse")
    public void tableFill(StandardMultipartHttpServletRequest request, HttpServletResponse response)throws Exception{
        if (request.getFileMap().isEmpty()) {
            PrintWriter out;
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/json; charset=utf-8");
            out = response.getWriter();
            ObjectMapper mapper = new ObjectMapper();
            out.write(mapper.writeValueAsString("请上传正确格式的文件"));
        }else{
            for (MultipartFile multiFile : request.getFileMap().values()) {
                InputStream ins;
                if (!multiFile.isEmpty()&&multiFile.getOriginalFilename()!=null) {
                    ins = multiFile.getInputStream();
                }else{
                    throw new RuntimeException("文件内容为空");
                }
                Workbook workbook = new XSSFWorkbook(ins);
                Sheet sheet = workbook.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                List<String> sql = new ArrayList<>();

                String sqlCon = "";

                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    String insert = sqlProcessDistrict(row);
                    sqlCon = sqlCon + insert;

                }
                response.addHeader("Content-Disposition", "attachment;filename="
                        + new String("Insert.txt".getBytes(StandardCharsets.UTF_8),"ISO8859-1"));
//                response.addHeader("content-type","application/json;charset=utf-8");
                response.setCharacterEncoding("utf-8");
                response.reset();
                FileOutputStream outputStream = new FileOutputStream("D:/insert.txt");
                outputStream.write(sqlCon.getBytes(StandardCharsets.UTF_8));
                outputStream.flush();
                outputStream.close();
            }
        }

    }


    private String sqlProcessor(Row row){
        Iterator<Cell> cellIterator = row.cellIterator();
        int count = 0;
        int last = 3;
        Map<String,String> paramMap = new HashMap<>();

        while(cellIterator.hasNext()){
            Cell cell = cellIterator.next();

            switch(cell.getCellType()){
                case 1:
                    count++;
                    if (count==1){
                        paramMap.put("code",String.valueOf((int)cell.getNumericCellValue()));
                    }else if (count==2){
                        paramMap.put("name",String.valueOf((int)cell.getNumericCellValue()));
                    }else{
                        paramMap.put("remark",String.valueOf((int)cell.getNumericCellValue()));
                    }
                    break;
                case 0:
                    count++;
                    if (count==1){
                        paramMap.put("code",cell.getStringCellValue());
                    }else if (count==2){
                        paramMap.put("name",cell.getStringCellValue());
                    }else{
                        paramMap.put("remark",cell.getStringCellValue());
                    }
                    break;
                default:break;
            }
            if (count == last){
                break;
            }
        }
        String code = paramMap.get("code");
        if (code.endsWith("00")){
            code = code.substring(0,code.length()-2);
        }
        if (code.endsWith("00")){
            code = code.substring(0,code.length()-2);
        }

        String insert = "INSERT INTO FOWK_DICT_ITEM " +
                "(DICT_CODE, CODE, NAME, NAME_I18N_CODE, VALUE, SORT_CODE, HOTSPOT, CORRELATION, STATUS, SUMMARY, REVISION, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME)\n" +
                "VALUES('Industry', '"+paramMap.get("code")+"', '"+paramMap.get("name")+"', 'Industry."+paramMap.get("code")+"', NULL, '"+code+"', 0, NULL, '1', '"+paramMap.get("remark")+"', 0, 'qcyang', TIMESTAMP '2022-09-19 00:00:00.000000', 'qcyang', TIMESTAMP '2022-09-19 00:00:00.000000');\n";
        String doubleLan = "\""+paramMap.get("code")+"\""+":"+"\""+paramMap.get("name")+"\",\n";
        System.out.println(insert);
        return doubleLan;
    }

//    public static void main(String[] args) {
//        String code = "012312100";
//        if (code.endsWith("00")){
//            code = code.substring(0,code.length()-2);
//        }
//        if (code.endsWith("00")){
//            code = code.substring(0,code.length()-2);
//        }
//        System.out.println(code);
//    }

    private String sqlProcessDistrict(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();
        int count = 0;
        int last = 2;
        Map<String,String> paramMap = new HashMap<>();

        while(cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
                case 1:
                    count++;
                    if (count == 1) {
                        paramMap.put("name", String.valueOf((int) cell.getNumericCellValue()));
                    } else if (count == 2) {
                        paramMap.put("code", String.valueOf((int) cell.getNumericCellValue()));
                        if (paramMap.get("name").equals(paramMap.get("code"))) {
                            last = 3;
                        }
                    } else {
                        paramMap.put("code", String.valueOf((int) cell.getNumericCellValue()));
                    }
                    break;
                case 0:
                    count++;
                    if (count == 1) {
                        paramMap.put("name", cell.getStringCellValue());
                    } else if (count == 2) {
                        paramMap.put("code", cell.getStringCellValue());
                        if (paramMap.get("name").equals(paramMap.get("code"))) {
                            last = 3;
                        }
                    } else {
                        paramMap.put("code", cell.getStringCellValue());
                    }
                    break;
                default:
                    break;
            }
            if (count == last) {
                break;
            }
        }

        String code = paramMap.get("code");
        if (code.endsWith("00")){
            code = code.substring(0,code.length()-2);
        }
        if (code.endsWith("00")){
            code = code.substring(0,code.length()-2);
        }

            String insert = "INSERT INTO FOWK_DICT_ITEM " +
                    "(DICT_CODE, CODE, NAME, NAME_I18N_CODE, VALUE, SORT_CODE, HOTSPOT, CORRELATION, STATUS, SUMMARY, REVISION, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME) " +
                    "VALUES('District', '" + paramMap.get("code") + "', '" + paramMap.get("name") + "', 'District." + paramMap.get("code") + "', NULL, '" + code + "', 0, NULL, '1', NULL, 0, 'qcyang', TIMESTAMP '2022-09-20 00:00:00.000000', 'qcyang', TIMESTAMP '2022-09-20 00:00:00.000000');\n";
//            System.out.println(insert);
        String doubleLan = "\""+paramMap.get("code")+"\""+":"+"\""+paramMap.get("name")+"\",\n";

        return insert;
    }
}
